var grades = require("./mark_model").grade
const XLSX = require("xlsx");
const xlsx = require("node-xlsx");
const fs = require("fs")

var dataToBeExported = []

var getAGrade = (data, j) => {
  grades.find({
    exam_id: "5bbcc04e8db9e0582d72dbff",
    studentId: data[j][3],
    name: data[j][4]
  }).sort({ '_id': -1 }).limit(1).exec((err, docs) => {
    if (err) {
      console.log(err)
    } else {
      console.log(j)
      if (docs.length != 0) {
        var mark = 0
        for (var n = 0; n < docs[0].grades.length; n++) {
          if (docs[0].grades[n] != "") {
            mark += parseInt(docs[0].grades[n])
          }
        }
        if(mark != 0 ){
          dataToBeExported[j][9] = mark
        }else {
          dataToBeExported[j][9] = ""
        }
        dataToBeExported[j][9] = mark
        console.log( mark)
      }

      if (j < 865) {
        getAGrade(data, j + 1)
      } else {
        console.log(dataToBeExported)
        writeXls(dataToBeExported)
      }
      
    }
  })
}


function writeXls(datas) {
  var buffer = xlsx.build([
    { "name": "marks", "data": datas }
  ]);
  fs.writeFileSync("2018_2_mark.xlsx", buffer, { 'flag': 'w' });
}



function importExcel(filePath, callback) {
  var data = [];
  var err = null;
  try {
    // Everything went fine
    var workbook = XLSX.readFile(filePath);　//整个　excel　文档
    var sheetNames = workbook.SheetNames; //获取所有工作薄名

    //console.log(sheetNames);

    //解析
    var sheet1 = workbook.Sheets[sheetNames[0]]; //根据工作薄名获取工作薄

    /*
     sheet1['!ref']　获取工作薄的有效范围　'A1:C20'
     XLSX.utils.decode_range 将有效范围转为　range对象
     range: {s: {r:0, c:0}, e: {r:10, 3}}
     */
    var range = XLSX.utils.decode_range(sheet1['!ref']);

    //循环获取单元格值
    for (var R = range.s.r; R <= range.e.r; ++R) {
      var row = [], flag = false;
      for (var C = range.s.c; C <= range.e.c; ++C) {
        var row_value = null;
        var cell_address = { c: C, r: R }; //获取单元格地址
        var cell = XLSX.utils.encode_cell(cell_address); //根据单元格地址获取单元格
        if (sheet1[cell]) //获取单元格值
          row_value = sheet1[cell].v;
        else
          row_value = '';
        row.push(row_value);
      }
      //判断整行是否都为空，是则去掉
      for (var i = 0; i < row.length; i++) {
        if (row[i] != '') {
          flag = true;
          break;
        }
      }
      if (flag) data.push(row);
    }
  } catch (e) {
    err = '解析出错' + e.toString();
  }

  callback(err, data);
}



importExcel('2018.xlsx', function (err, data) {
  if (err) {
    console.log(err);
  } else {
    console.log(data)
    dataToBeExported = data
    dataToBeExported[0][9] = "得分"
    getAGrade(data, 1)
  }
})